﻿
Partial Class WebForm_ManageTCUploadExcel_ManageTCUploadExcel
    Inherits System.Web.UI.UserControl

#Region "事件"
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Me.BindDDLUploadType()
            Me.BindVersion()
            Me.BindDDLDivision()
            Me.BindDDLSignature()
            Me.BindDDLEdition()
            Me.BindDDLCustomerGroup()
        End If
    End Sub

    Protected Sub ddlDivision_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlDivision.SelectedIndexChanged
        Me.BindDDLSignature()
        Me.BindDDLCustomerGroup()
    End Sub
    Protected Sub ddlSignature_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlSignature.SelectedIndexChanged
        Me.BindDDLCustomerGroup()
    End Sub
    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click

        If Not FileUpload1.HasFile Then
            MyObj.ShowMessage("no file")
            Return
        ElseIf IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper() <> ".XLS" AndAlso IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper() <> ".XLSX" Then
            MyObj.ShowMessage("Import failed!!Please select the file extension (xls/xlsx) file!!")
            Return
        End If

        '檢查資料表是否存在
        Dim TableName As String = Me.HeaderTB & Me.ddlVersion.SelectedValue
        Dim dat As New clsData
        Dim sql As New MyStringBuilder
        sql.AppendFormat("Select * from sysobjects where name='{0}'", TableName)
        If Not dat.GetData(sql.ToString()).Rows.Count > 0 Then
            MyObj.ShowMessage("Table missed.")
            Return
        End If

        '檢查此版本的Signature是否已經被Locked
        Dim vManager As New VersionManager(Me.ddlVersion.SelectedValue.Replace("TCReport", ""), Me.ddlSignature.SelectedValue, Me.HeaderTB)
        If vManager.Locked = True Then
            MyObj.ShowMessage("Version Locked.")
            Return
        End If

        '上傳暫存檔
        Dim clsRandom As New clsRandom()
        Dim newName As String = Server.MapPath("~/") & "/tmp/" & clsRandom.Generate()
        FileUpload1.PostedFile.SaveAs(newName)

        Dim ExcelImportCheck As New ExcelImportCheck()
        Dim dt As New Data.DataTable


        '檢查工作表名稱
        Try
            If ExcelImportCheck.LoadData(dt, newName, "TCUpload", IO.Path.GetExtension(FileUpload1.PostedFile.FileName).ToUpper()) = False Then
                Return
            End If
        Catch ex As Exception
            MyObj.ShowMessage("Please check the file format!!!")
            Exit Sub
        End Try

        '重新命名欄位名稱及清空值
        Dim MonthAdd As Integer = 0
        Dim MonthCount As Integer
        Dim ErrorCount As Integer = 0
        Dim FilterValue As String = ""
        '存月份欄位字串
        Dim MonthString As String = ""
        Dim MonthScope As Integer = 0
        Dim TempDate As Date
        Dim GetMonth As String = ""

        Dim BaseColumns As Integer = 1 '非月份欄位有4欄
        '如果月份欄位名稱填入非日期或是為F開頭的欄位,則直接刪掉該欄位
        For i As Integer = dt.Columns.Count - 1 To BaseColumns Step -1
            If Not IsDate(dt.Columns(i).ColumnName) Then
                If Left(dt.Columns(i).ColumnName.ToString(), 1) = "F" Then
                    dt.Columns.RemoveAt(i)
                Else
                    MyObj.ShowMessage("Column [{0}] is out of scope.", dt.Columns(i).ColumnName)
                    Return
                End If
            End If
        Next

        If IsNumeric(TableName.Replace(Me.HeaderTB & "TCReport", "")) Then
            'MonthCount = DateTime.Now.Month
            MonthCount = Right(TableName, 2)
        Else
            If TableName.IndexOf("B") <> -1 Then
                MonthCount = 1
            Else
                Dim tcManager As New TCReportReferManager(Me.ddlSignature.SelectedValue, TableName)
                MonthCount = Right(tcManager.GetCurrent(), 2)
            End If

            MonthCount = IIf(MonthCount = 0, 1, MonthCount)
        End If
        MonthScope = 24 - MonthCount + 1 + 1  '計算當前月份到明年12月的範圍
        TempDate = New Date(Left(TableName.Replace(Me.HeaderTB & "TCReport", ""), 4), MonthCount, 1)
        If dt.Columns.Count >= 2 And dt.Columns.Count <= MonthScope Then
            For i As Integer = 0 To dt.Columns.Count - 1
                If i = 0 Then
                    dt.Columns(0).ColumnName = "ItemName"
                    FilterValue &= "convert(" & dt.Columns(0).ColumnName.ToString() & ",System.String)<>''"
                ElseIf i >= 1 Then

                    '    If MonthAdd = 0 Then
                    '        dt.Columns(i).ColumnName = "Mon" & MonthCount
                    '        MonthAdd = 1
                    '    Else
                    '        dt.Columns(i).ColumnName = "Mon" & MonthCount + MonthAdd
                    '        MonthAdd += 1
                    '    End If
                    '    MonthString &= dt.Columns(i).ColumnName & ","
                    '    FilterValue &= " or convert(" & dt.Columns(i).ColumnName.ToString() & ",System.String)<>''"
                    GetMonth = dt.Columns(i).ColumnName.Split("/")(1)
                    Dim YearGap As Integer = dt.Columns(i).ColumnName.Split("/")(0) - Left(TableName.Replace(Me.HeaderTB & "TCReport", ""), 4)
                    If YearGap < 0 OrElse (Int32.Parse(GetMonth) + YearGap * 12) > 24 Then
                        ErrorCount += 1
                        MyObj.ShowMessage("{0} is out of scope.", dt.Columns(i).ColumnName)
                        Return
                    End If
                    dt.Columns(i).ColumnName = "Mon" & (Int32.Parse(GetMonth) + YearGap * 12)
                    MonthString &= dt.Columns(i).ColumnName & ","
                    FilterValue &= " or convert(" & dt.Columns(i).ColumnName.ToString() & ",System.String)<>''"
                End If

            Next
            '新增Error欄位
            dt.Columns.Add("Error", System.Type.GetType("System.String"))

            Dim MyDefaultView As Data.DataView = dt.DefaultView
            MyDefaultView.RowFilter = FilterValue
            dt = MyDefaultView.ToTable()
        ElseIf dt.Columns.Count < 2 Then
            ErrorCount += 1
            MyObj.ShowMessage("Your column count should greater than 2.")
            Return
        Else
            ErrorCount += 1
            MyObj.ShowMessage("Your column count should smaller than " & MonthScope & ".")
            Return
        End If
        '將欄位字串最後一個多的逗號去掉
        MonthString = MonthString.Substring(0, MonthString.Length - 1)


        '開始檢查資料
        Dim ItemName As String
        For k As Integer = 0 To dt.Rows.Count - 1
            ItemName = dt.Rows(k)("ItemName").ToString().Trim()
            '每個月份檢查
            For Each mon As String In MonthString.Split(",")
                If dt.Rows(k)(mon).ToString() <> "" Then
                    '不可為非數字　
                    If IsNumeric(dt.Rows(k)(mon).ToString()) = False Then
                        dt.Rows(k)("Error") = "Your column should be numeric."
                        Exit For
                    End If
                End If

                '比對ItemName範圍
                If ItemName <> "" Then
                    If MyObj.CheckItemName(ItemName) = False Then
                        dt.Rows(k)("Error") = "The Grade is out of your scope."
                        Exit For
                    End If
                End If
            Next
            If dt.Rows(k)("Error").ToString() <> "" Then
                ErrorCount += 1
                Continue For
            End If
        Next


        '有異常則不寫入資料
        If (ErrorCount > 0) Then
            MyObj.ShowMessage("Upload Failed.Data Error Count:" & ErrorCount)
        Else
            If dt.Rows.Count > 0 Then
                Dim MyRandom As New clsRandom
                Dim RandomCode As String = MyRandom.Generate(8)

                '新增欄位
                dt.Columns.Add("Hierarchy", System.Type.GetType("System.String"))
                Dim Mydc As New DataColumn("RandomCode", System.Type.GetType("System.String"))
                Mydc.DefaultValue = RandomCode
                dt.Columns.Add(Mydc)

                Mydc = New DataColumn("ChannelID", System.Type.GetType("System.String"))
                Mydc.DefaultValue = Me.ddlCustomerGroup.SelectedValue
                dt.Columns.Add(Mydc)



                Using cn As New SqlConnection(ConfigurationManager.ConnectionStrings("connStr").ConnectionString)
                    cn.Open()
                    Using copy As New SqlBulkCopy(cn)
                        For i As Integer = 0 To dt.Columns.Count - 1
                            copy.ColumnMappings.Add(dt.Columns(i).ColumnName, dt.Columns(i).ColumnName)
                        Next
                        copy.DestinationTableName = "TCReportU"
                        copy.WriteToServer(dt)
                    End Using
                End Using

                dt.Columns.Remove("Hierarchy")
                dt.Columns.Remove("RandomCode")
                dt.Columns.Remove("ChannelID")
                Try
                    sql.Length = 0
                    sql.AppendFormat("exec ImportFinanceForecast @RandomCode='{0}' ", RandomCode)
                    sql.AppendFormat(",@TableName='{0}' ", TableName)
                    sql.AppendFormat(",@Division='{0}' ", Me.ddlDivision.SelectedValue)
                    sql.AppendFormat(",@Signature='{0}' ", Me.ddlSignature.SelectedValue)
                    sql.AppendFormat(",@Value='{0}' ", ddlVersion.SelectedValue.Substring(8, ddlVersion.SelectedValue.Length - 8).ToString())
                    sql.AppendFormat(",@CustomerGroup='{0}' ", Me.ddlCustomerGroup.SelectedValue)
                    sql.AppendFormat(",@EmployeeID='{0}' ", clsAuth.GetUserID())

                    dat.AccData(sql.ToString())
                    MyObj.ShowMessage("Upload Successful.Data Success Count:" & dt.Rows.Count)

                Catch ex As Exception
                    MyObj.WriteLog(ex.ToString())
                    MyObj.ShowMessage("Upload Failed.")
                End Try

            Else
                MyObj.ShowMessage("Upload Failed.")
            End If
        End If


        '改顯示欄位名稱
        Dim CountAdd As Integer = 0
        Dim ErrorFilterValue As String = ""
        For a As Integer = 0 To dt.Columns.Count - 1
            If a = 0 Then
                dt.Columns(0).ColumnName = "Grade"


            ElseIf a >= 1 And a <> dt.Columns.Count - 1 Then

                If CountAdd = 0 Then
                    dt.Columns(a).ColumnName = TempDate.ToString("yyyy/MM")
                    CountAdd = 1
                Else
                    dt.Columns(a).ColumnName = TempDate.AddMonths(CountAdd).ToString("yyyy/MM")
                    CountAdd += 1
                End If

            ElseIf a = dt.Columns.Count - 1 Then

                dt.Columns(a).ColumnName = "Error"
                dt.Columns(a).SetOrdinal(0)

            End If

        Next

        dt.DefaultView.RowFilter = "convert(Error,System.String)<>''"
        TCGVDT = dt.DefaultView.ToTable

        Me.BindGV()
    End Sub
    Protected Sub gv_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gv.PageIndexChanging
        Me.gv.PageIndex = e.NewPageIndex
        Me.BindGV()
    End Sub
#End Region

#Region "繫結資料"
    Protected Sub BindDDLUploadType()
        ddlUploadType.DataTextField = "Text"
        ddlUploadType.DataValueField = "Value"
        ddlUploadType.DataSource = MyObj.GetCodeMappingData("ForecastUpload", "UploadType")
        ddlUploadType.DataBind()

        If ddlUploadType.Items.FindByValue("PLV") IsNot Nothing Then
            ddlUploadType.Items.Remove(ddlUploadType.Items.FindByValue("PLV"))
        End If
    End Sub

    Protected Sub BindDDLCustomerGroup()
        Dim cmanager As New ChannelManager
        ddlCustomerGroup.DataSource = cmanager.GetAllByDivision(Me.ddlDivision.SelectedValue, Me.ddlSignature.SelectedValue)
        ddlCustomerGroup.DataTextField = "ChannelName"
        ddlCustomerGroup.DataValueField = "Channel"
        ddlCustomerGroup.DataBind()
        ddlCustomerGroup.Items.Insert(0, MyObj.GetDDLSelectAllItem())
    End Sub
    Protected Sub BindVersion()
        Me.ddlVersion.Items.Clear()
        Me.ddlVersion.Items.Add(New ListItem(DateTime.Now.ToString("yyyy/MM"), "TCReport" & DateTime.Now.ToString("yyyyMM")))
        Me.ddlVersion.Items.Add(New ListItem(DateTime.Now.AddMonths(1).ToString("yyyy/MM"), "TCReport" & DateTime.Now.AddMonths(1).ToString("yyyyMM")))
        Me.ddlVersion.Items.Add(New ListItem(DateTime.Now.ToString("yyyy") & "Budget", "TCReport" & DateTime.Now.ToString("yyyy") & "B"))
        Me.ddlVersion.Items.Add(New ListItem(DateTime.Now.ToString("yyyy") & "Trend", "TCReport" & DateTime.Now.ToString("yyyy") & "T"))
        Me.ddlVersion.Items.Add(New ListItem(DateTime.Now.AddYears(1).ToString("yyyy") & "Budget", "TCReport" & DateTime.Now.AddYears(1).ToString("yyyy") & "B"))
        Me.ddlVersion.SelectedIndex = -1
    End Sub
    Private Sub BindDDLDivision()
        Dim ddlControl As DropDownList = Me.ddlDivision
        Dim dvManager As New DivisionManager

        ddlControl.DataTextField = "Division"
        ddlControl.DataValueField = "Division"
        ddlControl.DataSource = dvManager.GetAllDivision()
        ddlControl.DataBind()
    End Sub

    Private Sub BindDDLSignature()
        Dim ddlControl As DropDownList = Me.ddlSignature
        Dim stManager As New SignatureManager

        ddlControl.DataTextField = "Description"
        ddlControl.DataValueField = "Signature"
        ddlControl.DataSource = stManager.GetAllSignature(Me.ddlDivision.SelectedValue)
        ddlControl.DataBind()
    End Sub

    Protected Sub BindDDLEdition()
        ddlEdition.DataTextField = "Text"
        ddlEdition.DataValueField = "Value"
        ddlEdition.DataSource = MyObj.GetCodeMappingData("Product", "Version")
        ddlEdition.DataBind()
        ddlEdition.Items.Insert(0, MyObj.GetDDLSelectAllItem())
        'ddlEdition.SelectedValue = "S"
    End Sub
    Protected Sub BindGV()
        gv.DataSource = TCGVDT
        gv.DataBind()
        If gv.Rows.Count > 0 Then
            gv.Visible = True
        Else
            gv.Visible = False
        End If
    End Sub
#End Region

#Region "屬性"
    Public Property TCGVDT() As Data.DataTable
        Get
            Return ViewState("TCGVDT")
        End Get
        Set(ByVal value As Data.DataTable)
            ViewState("TCGVDT") = value
        End Set
    End Property

    Public ReadOnly Property HeaderTB()
        Get
            Select Case Me.ddlUploadType.SelectedValue
                Case "SellOut"
                    Return "SO_"
                Case "PLV"
                    Return "PLV_"
                Case Else
                    Return ""
            End Select
        End Get
    End Property
#End Region
End Class
